using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;

using Pegasus.Diagnostics;

namespace Pegasus.Data.SqlClient
{
	/// <summary>
	/// This is a helper class is used to access a given database on a given server.
	/// </summary>
	/// <remarks>
	/// The class hold the credentidals and settings for the database.  To execute
	/// a sql statement call the CreateDatabaseAccess() method to get the access
	/// object.
	/// <code>
	/// public class MyApp
	/// {
	///		private SqlDatabaseConnection m_conn;
	///		
	///		public MyApp()
	///		{
	///			m_conn = new SqlDatabaseConnection( "sqlserver", "thedatabase", "myacount", "mypassword" );
	///		}
	///		
	///		public void DoSomeDatabaseStuff( int aNumber )
	///		{
	///			SqlDatabaseAccess dba = m_conn.CreateDatabaseAccess()
	///			dba.SqlNonQuery( "INSERT INTO MyTable ( number ) VALUES( '{0}' )", aNumber );
	///			dba.Close();
	///		}
	///	}
	/// </code>
	/// </remarks>
	public class SqlDatabaseConnection
	{
		// Local Instance Values
		private string m_server;
		private string m_database;
		private string m_username;
		private string m_password;
		private string m_connectionString;
		private int m_connectionTimeout = 30;

		private Hashtable m_storedProcParameterTable = new Hashtable();

		// Notification Handler Values
		private bool startedSqlDependency = false;
		private Dictionary<string, SqlDatabaseNotification> m_notificationTable = new Dictionary<string, SqlDatabaseNotification>();


		/// <summary>
		/// Default Constructor.
		/// </summary>
		/// <param name="server">The name of the SQL server</param>
		/// <param name="database">The name of the database</param>
		/// <param name="username">The SQL account username</param>
		/// <param name="password">The SQL account password</param>
		/// <remarks>
		/// If the username is null then the object will use Windows authentication to access
		/// the database, otherwise it will use SQL authentication.
		/// If the system this using windows authentication then the password parameter is ignored.
		/// </remarks>
		public SqlDatabaseConnection( string server, string database, string username, string password )
		{
			// Check params
			ParamCode.AssertNotEmpty( server, "server" );
			ParamCode.AssertNotEmpty( database, "database" );
			ParamCode.AssertNullOrNotEmpty( username, "username" );
			// password can be null, blank, or a string

			m_server = server;
			m_database = database;
			m_username = username;
			m_password = password;

			BuildConnectionString();
		}

		/// <summary>
		/// Constructor that uses the Windows authentication to access the database.
		/// </summary>
		/// <param name="server">The name of the SQL server</param>
		/// <param name="database">The name of the database</param>
		public SqlDatabaseConnection( string server, string database ) :
			this( server, database, null, null )
		{
		}

        /// <summary>
        /// Constructor that uses the given connection string.
        /// </summary>
        public SqlDatabaseConnection( string connectionString )
        {
            m_connectionString = connectionString;
        }

		/// <summary>
		/// The SQL server name.
		/// </summary>
		public string Server
		{
			get
			{
				return m_server;
			}
		}

		/// <summary>
		/// The name of the SQL database.
		/// </summary>
		public string Database
		{
			get
			{
				return m_database;
			}
		}

		/// <summary>
		/// The SQL account username.  If this value is null then the database
		/// uses Windows authentication to access the database.
		/// </summary>
		public string Username
		{
			get
			{
				return m_username;
			}
		}

		/// <summary>
		/// The SQL account password. If Windows authentication is used then
		/// this value is ignored and should be null. 
		/// </summary>
		public string Password
		{
			get
			{
				return m_password;
			}
		}

		/// <summary>
		/// The connection string used to access the database.
		/// </summary>
		public string ConnectionString
		{
			get
			{
				return m_connectionString;
			}
		}

		/// <summary>
		/// Gets or sets the connection timeout. The time (in seconds) to wait for a connection to open. The default value is 15 seconds.
		/// </summary>
		/// <value>The connection timeout.</value>
		public int ConnectionTimeout
		{
			get
			{
				return m_connectionTimeout;
			}
		
			set
			{
				m_connectionTimeout = value;
			}
		}

		/// <summary>
		/// Get a database access object that is not trasacted.
		/// </summary>
		/// <returns>The database access object for getting data from the system.</returns>
		public SqlDatabaseAccess CreateDatabaseAccess()
		{
			return new SqlDatabaseAccess( this, false );
		}

		/// <summary>
		/// Get a database access object.
		/// </summary>
		/// <param name="transacted">True if the database access needs to be transacted or not</param>
		/// <returns>The database access object for getting data from the system.</returns>
		public SqlDatabaseAccess CreateDatabaseAccess( bool transacted )
		{
			return new SqlDatabaseAccess( this, transacted );
		}

		/// <summary>
		/// Overrides the build in connection string.
		/// </summary>
		/// <param name="connectionString">The connection string.</param>
		public void OverrideConnectionString( string connectionString )
		{
			m_connectionString = connectionString;
		}

		/// <summary>
		/// Registers for notification using an SQL statement.
		/// </summary>
		/// <param name="eventHandler">The event handler.</param>
		/// <param name="sql">The SQL statement.</param>
		/// <remarks>
		///	Note: This functionality only work on SQL Server 2005 and you must enable
		/// the database to support service broker events using the following sql statement.
		/// 
		///		<code>ALTER DATABASE TheDatabaseName SET ENABLE_BROKER</code>
		/// 
		/// </remarks>
		public void RegisterForNotificationSql( OnChangeEventHandler eventHandler, string sql )
		{
			// Check Parameters
			ParamCode.AssertNotNull( eventHandler, "eventHandler" );
			ParamCode.AssertNotEmpty( sql, "sql" );

			lock( m_notificationTable )
			{
				if( !startedSqlDependency )
				{
					SqlDependency.Start( m_connectionString );
					startedSqlDependency = true;
				}

				if( m_notificationTable.ContainsKey( sql ) )
				{
					SqlDatabaseNotification notification = m_notificationTable[ sql ];
					notification.Add( eventHandler );
				}
				else
				{
					SqlDatabaseNotification notification = new SqlDatabaseNotification( m_connectionString, sql, eventHandler );
					m_notificationTable.Add( sql, notification );
				}
			}
		}

		/// <summary>
		/// Unregisters for notification.
		/// </summary>
		/// <param name="eventHandler">The event handler.</param>
		public void UnregisterForNotification( OnChangeEventHandler eventHandler )
		{
			// Check Parameters
			ParamCode.AssertNotNull( eventHandler, "eventHandler" );

			lock( m_notificationTable )
			{
				List<SqlDatabaseNotification> removeList = new List<SqlDatabaseNotification>();

				foreach( SqlDatabaseNotification notification in m_notificationTable.Values )
				{
					notification.Remove( eventHandler );
					if( notification.Handlers == 1 )
					{
						removeList.Add( notification );
					}
				}

				// Remove and dispose of the objects that have no handlers
				foreach( SqlDatabaseNotification notification in removeList )
				{
					m_notificationTable.Remove( notification.SQL );
					notification.Dispose();
				}

				// If the table is empty then stop the dependency objects
				if( m_notificationTable.Count == 0 )
				{
					SqlDependency.Stop( m_connectionString );
					startedSqlDependency = false;
				}
			}
		}

		/// <summary>
		/// Build the connection string from the object properties
		/// </summary>
		protected virtual void BuildConnectionString()
		{
			m_connectionString = string.Format( "Data Source={0};Initial Catalog={1};Workstation ID={2};Connection Timeout={3};", m_server, m_database, Environment.MachineName, m_connectionTimeout );

			if( m_username != null && m_username.Length > 0 )
			{
				m_connectionString += string.Format( "User ID={0};pwd={1}", m_username, m_password );
			}
			else
			{
				m_connectionString += "Integrated Security=SSPI;";
			}
		}

		/// <summary>
		/// Called by the SQLDatabaseAccess object to get the cached parameter list
		/// </summary>
		/// <param name="proc">The name of the proc</param>
		/// <returns>A hashtable of sql parameter object indexed by thier names</returns>
		internal virtual ArrayList GetStoredProcParameterList( string proc )
		{
			// Check params
			ParamCode.AssertNotEmpty( proc, "proc" );

			// Do we alredy have the parameter list
			lock( this )
			{
				if( m_storedProcParameterTable.Contains( proc ) )
				{
					return (ArrayList) m_storedProcParameterTable[ proc ];
				}
			}

			ArrayList paramList = new ArrayList();

			SqlConnection sqlConnection = new SqlConnection( ConnectionString );
			sqlConnection.Open();

			try
			{
				SqlCommand cmd = sqlConnection.CreateCommand();
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.CommandText = proc;
					
				SqlCommandBuilder.DeriveParameters( cmd );
				paramList.AddRange( cmd.Parameters );
			}
			finally
			{
				sqlConnection.Close();
			}

			// Cache and return the parameter list
			lock( this )
			{
				m_storedProcParameterTable[ proc ] = paramList;
			}

			return paramList;
		}

		/// <summary>
		/// Clear out the chached parameter list.
		/// </summary>
		/// <param name="proc">The name of the stored procedure to clear</param>
		internal virtual void ClearStoredProcParameterList( string proc )
		{
			lock( this )
			{
				m_storedProcParameterTable.Remove( proc );
			}
		}
	}
}
